package tools

import (
	"database/sql"
	"errors"
	"fmt"
	"log"
	p "path"
	"runtime"
	"strings"

	_ "github.com/go-sql-driver/mysql"
)

type Table struct {
	Id        int     `json:"id"`
	Ip        string  `json:"ip"`
	Metric    string  `json:"metric"`
	Threshold float64 `json:"threshold"`
	AlertType int32   `json:"alertType"` // 3：Warn   2：Severe   1：Fatal
}

var (
	db       *sql.DB
	dbConfig DBConfig
)

// InitDB 初始化数据库连接
func InitDB() (err error) {
	_, file, _, _ := runtime.Caller(0)
	path := p.Dir(p.Dir(file)) + "/configs/sql.json"
	// 加载json配置文件
	dbConfig.LoadJsonConfig(path)
	// 得到配置文件中的值
	username, password, ip, port, dbName := dbConfig.GetDBConfig()
	// 构建连接："用户名:密码@tcp(IP:端口)/数据库?charset=utf8"
	dsn := strings.Join([]string{username, ":", password, "@tcp(", ip, ":", port, ")/", dbName, "?charset=utf8"}, "")
	// 打开数据库,前者是驱动名，所以要导入： _ "github.com/go-sql-driver/mysql"
	// 注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		fmt.Println("open mysql failed,", err)
		return err
	}
	// 尝试与数据库建立连接（校验dsn是否正确）
	err = db.Ping()
	if err != nil {
		log.Println("Ping failed, ", err)
		return err
	}
	// 设置数据库最大连接数
	db.SetConnMaxLifetime(20)
	// 设置上数据库最大闲置连接数
	db.SetMaxIdleConns(10)
	// fmt.Println("connect success!")
	return nil
}

// InsertData 往数据库中添加数据
func (data *Table) InsertData() error {

	// 插入DB之前判断数据库中是否已经存在，ip、metric和alertType都一样则表示已经存在
	if data.IsHasData() {
		log.Println("【Error】The data has existed in the database...")
		return nil
	}
	// 开启事务
	tx, err := db.Begin()
	if err != nil {
		log.Println("【Error】tx start failed: ", err)
		return err
	}
	// 准备sql语句
	stmt, err := tx.Prepare("insert into " + dbConfig.TblName + " (ip,metric,threshold,alertType) values (?,?,?,?)")
	if err != nil {
		log.Println("【Error】tx prepare failed: ", err)
		return err
	}
	defer stmt.Close()

	// 将参数传递到sql语句中并且执行
	res, err := stmt.Exec(data.Ip, data.Metric, data.Threshold, data.AlertType)
	if err != nil {
		log.Println("【Error】Exec failed: ", err)
		return err
	}
	// 将事务提交
	err = tx.Commit()
	if err != nil {
		log.Println("tx commit failed: ", err)
		return err
	}
	rowsAffected, err := res.RowsAffected()
	if err != nil {
		log.Println("【Error】res.RowsAffected() failed: ", err)
		return err
	}
	fmt.Printf("%d rows of data have been inserted\n", rowsAffected)
	return nil
}

// DeleteData 通过 ip metric 字段删除数据库中的行（可能有多行）
func (data *Table) DeleteData() error {
	InitDB()
	if !data.IsHasData2() {
		log.Println("\nthe data does not exist in the database")
		return errors.New("data does not exist")
	}
	//开启事务
	tx, err := db.Begin()
	if err != nil {
		log.Println("【Error】tx start failed: ", err)
		return err
	}

	// 准备sql语句
	stmt, err := tx.Prepare("DELETE FROM " + dbConfig.TblName + " WHERE ip=? AND metric=?")
	if err != nil {
		log.Println("【Error】tx prepare failed: ", err)
		return err
	}
	// 设置参数以及执行sql语句
	res, err := stmt.Exec(data.Ip, data.Metric)
	if err != nil {
		log.Println("【Error】stmt.Exec failed: ", err)
		return err
	}
	defer stmt.Close()
	// 提交事务
	err = tx.Commit()
	if err != nil {
		log.Println("【Error】tx.Commit failed: ", err)
		return err
	}
	rowsAffected, err := res.RowsAffected()
	if err != nil {
		log.Println("【Error】res.RowsAffected() failed: ", err)
		return err
	}
	fmt.Printf("%d rows of data have been deleted\n", rowsAffected)
	return nil
}

// UpdateData 通过 ip metric 字段修改数据库中的行
func (data *Table) UpdateData() error {
	InitDB()
	// 开启事务
	tx, err := db.Begin()
	if err != nil {
		log.Println("【Error】tx start failed: ", err)
		return err
	}
	// 准备sql语句，通过id得到该行记录
	stmt, err := tx.Prepare("UPDATE " + dbConfig.TblName + " SET ip=?, metric=?, threshold=?, alertType=? WHERE id=?")
	if err != nil {
		log.Println("【Error】tx prepare failed: ", err)
		return err
	}
	// 设置参数以及执行sql语句
	res, err := stmt.Exec(data.Ip, data.Metric, data.Threshold, data.AlertType, data.Id)
	if err != nil {
		log.Println("【Error】stmt.Exec failed: ", err)
		return err
	}
	defer stmt.Close()
	// 提交事务
	err = tx.Commit()
	if err != nil {
		log.Println("【Error】tx.Commit failed: ", err)
		return err
	}
	rowsAffected, err := res.RowsAffected()
	if err != nil {
		log.Println("【Error】res.RowsAffected() failed: ", err)
		return err
	}
	fmt.Printf("%d rows of data have been modified\n", rowsAffected)
	return nil
}

// IsHasData 查询数据库中是否含有该条记录
func (data *Table) IsHasData() bool {
	var has bool
	// 判断数据库中是否存在记录data，ip、metric都一样则表示已经存在
	err := db.QueryRow("select id from "+dbConfig.TblName+" where ip=? and metric=? and alertType=?",
		data.Ip, data.Metric, data.AlertType).Scan(&data.Id)
	if err != nil {
		if err == sql.ErrNoRows {
			has = false
		} else {
			log.Fatal(err)
		}
	} else {
		has = true
	}
	return has
}

func (data *Table) IsHasData2() bool {
	var has bool
	// 判断数据库中是否存在记录data，ip、metric都一样则表示已经存在
	err := db.QueryRow("select id from "+dbConfig.TblName+" where ip=? and metric=?",
		data.Ip, data.Metric).Scan(&data.Id)
	if err != nil {
		if err == sql.ErrNoRows {
			has = false
		} else {
			log.Fatal(err)
		}
	} else {
		has = true
	}
	return has
}

// GetDataByIpAndMetric 判断服务通过 ip 和 metric 得到阈值和告警类型（查数据库）
func GetDataByIpAndMetric(ip string, metric string) []*Table {
	InitDB()
	query := "SELECT * FROM " + dbConfig.TblName + " WHERE ip=? AND metric=? ORDER BY alertType ASC"
	// Query：查询数据库，得到多行数据
	rows, err := db.Query(query, ip, metric)
	if err != nil {
		log.Fatalf("db.Query failed, err: %v\n", err)
	}
	defer rows.Close()

	var res []*Table
	// 使用 rows.Next() 来遍历每行的数据
	for rows.Next() {
		var data Table // 构建 Data 对象
		// rows.Scan() 来将每一行中每一列的数据赋值给变量
		err := rows.Scan(&data.Id, &data.Ip, &data.Metric, &data.Threshold, &data.AlertType)
		if err != nil {
			log.Fatalf("rows.Scan failed, err: %v\n", err)
		}
		// 把信息加入到data切片中
		res = append(res, &Table{Id: data.Id, Ip: data.Ip, Metric: data.Metric, Threshold: data.Threshold, AlertType: data.AlertType})
	}
	return res
}
